﻿/* ***************************************************
	Người tạo: Thanh
	Ngày tạo: 4/6/2014
	Ngày sửa: 22/01/2016
	Nội dung: Thực hiện việc kiểm kê ngày 1/7 tự động	
*****************************************************/
ALTER procedure [dbo].[sp_T_KiemKe17]
as
begin
	delete from tblKiemKe where DotKiemKe=2
	INSERT INTO tblKiemKe
		SELECT 2, temp.ID_MatHang, temp.SLKK17Old, SLTang=temp.SLNhap17_11+temp.Nhap,
			SLGiam =temp.SLXuat17_11+temp.Xuat,
			temp.SLKK17Old +temp.SLNhap17_11+temp.Nhap -temp.SLXuat17_11 - temp.Xuat , temp.SLKK17Old +temp.SLNhap17_11+temp.Nhap -temp.SLXuat17_11 - temp.Xuat , 0,0,
			SLCCL1=
			case 
				when temp.ID_CCL =1 then temp.SLKK17Old +temp.SLNhap17_11+temp.Nhap -temp.SLXuat17_11 - temp.Xuat 
				else 0 
			end,
			SLCCL2=
			case 
				when temp.ID_CCL =2 then temp.SLKK17Old +temp.SLNhap17_11+temp.Nhap -temp.SLXuat17_11 - temp.Xuat 
				else 0 
			end,
			SLCCL3a=
			case 
				when temp.ID_CCL =3 then temp.SLKK17Old +temp.SLNhap17_11+temp.Nhap -temp.SLXuat17_11 - temp.Xuat 
				else 0 
			end,
			SLCCL3b=
			case 
				when temp.ID_CCL =4 then temp.SLKK17Old +temp.SLNhap17_11+temp.Nhap -temp.SLXuat17_11 - temp.Xuat 
				else 0 
			end,
			0,0,temp.SLKK17Old +temp.SLNhap17_11+temp.Nhap -temp.SLXuat17_11 - temp.Xuat ,0,0,null
		FROM 
		(
				SELECT t.ID_MatHang, t.SLKK17Old, t.Gia, t.ID_CCL, t.SLNhap17_11, t.SLXuat17_11, 
			sum(nx.Nhap) as Nhap, sum(nx.Xuat) as Xuat, (t.SLKK17Old + sum(nx.Nhap) - sum(nx.Xuat)) as Ton
				FROM 
				(
					select ID_MatHang, 0 as Nhap, 0 as Xuat, SLKK17Old as Ton
					from tblTon
					union all 
					select ct.ID_MatHang, ct.SLKH as Nhap, 0 as Xuat, ct.SLKH as Ton
					from tblSLNX ct, tblDPNX p
					where ct.ID_DPNX= p.ID_DPNX and p.NX='N' and month(p.NgayPhieu)<7
					union all
					select ct.ID_MatHang, 0 as Nhap, ct.SLKH as Xuat, -ct.SLKH as Ton
					from tblSLNX ct, tblDPNX p
					where ct.ID_DPNX= p.ID_DPNX and p.NX='X' and month(p.NgayPhieu)<7
				) as nx , tblTon as t 
				where nx.ID_MatHang = t.ID_MatHang
				group by t.ID_MatHang, t.SLKK17Old, t.SLNhap17_11, t.SlXuat17_11, t.Gia, t.ID_CCL
			) as temp
end
